package com.neuedu.my12306.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import com.neuedu.my12306.dao.DBHelper;
import com.neuedu.my12306.dao.IUserInfoDao;
import com.neuedu.my12306.entity.UserInfo;

public class IUserInfoDaoImpl implements IUserInfoDao {
	
	@Override
	public UserInfo login(String userName, String userPass) {
		UserInfo userInfo = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "select username, userpass, truename, sex, province, city, cardtype, "
				+ "idnumber, passengertype, tips, birthdate,userPower,headImage from sysuser where userName=? and userPass=? ";
		
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userName);
			pstmt.setString(2, userPass);
			res = pstmt.executeQuery();
			if(res.next()) {
				//String userPass = res.getString("userpass");
				String trueName = res.getString("truename");
				String sex = res.getString("sex");
				String province = res.getString("province");
				String city = res.getString("city");
				String cardType = res.getString("cardtype");
				String idNumber = res.getString("idnumber");
				String passengerType = res.getString("passengertype");
				String tips = res.getString("tips");
				Date birthDate = res.getDate("birthdate");
				int userPower = res.getInt("userpower");
				String headImage = res.getString("headimage");
				userInfo = new UserInfo(userName, userPass, trueName, sex, province, city, cardType, idNumber, passengerType, tips, birthDate,userPower,headImage);
				
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		return userInfo;
	}

	@Override
	public boolean reg(UserInfo userInfo) {
		boolean ret=false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "insert into SYSUSER (username, userpass, truename, sex, province, city, cardtype, idnumber, passengertype, tips, birthdate,userPower,HEADIMAGE) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userInfo.getUserName());
			pstmt.setString(2, userInfo.getUserpass());
			pstmt.setString(3, userInfo.getTrueName());
			pstmt.setString(4, userInfo.getSex());
			pstmt.setString(5, userInfo.getProvince());
			pstmt.setString(6, userInfo.getCity());
			pstmt.setString(7, userInfo.getCardType());
			pstmt.setString(8, userInfo.getIdNumber());
			pstmt.setString(9, userInfo.getPassengerType());
			pstmt.setString(10, userInfo.getTips());
			//时间精度只保留年月日，可以使用java.sql.Date()保存数据，将util.Date转换为sql.Date
			pstmt.setDate(11, new java.sql.Date(userInfo.getBirthDate().getTime()));
			pstmt.setInt(12, userInfo.getUserPower());
			pstmt.setString(13, userInfo.getHeadImage());
			int count = pstmt.executeUpdate();
			if (count==1) {
				ret = true;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		
		return ret;
	}

	@Override
	public int findTotal() {
		int total = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "select count(*) from sysuser";
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			res = pstmt.executeQuery();
			if(res.next()) {
				total = res.getInt(1);//查询记录总数的sql返回一行一列数据，即返回记录总数
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		return total;
	}

	@Override
	public List<UserInfo> findUserInfos(int page, int size) {
		List<UserInfo> list = new ArrayList<UserInfo>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "select username, userpass, truename, sex, province, city, cardtype, idnumber,"
				+ " passengertype, tips, birthdate"
				+ " from ( select u.*,rownum rn from sysuser u  where rownum <=?) where rn >? ";
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, page*size);
			pstmt.setInt(2, (page-1)*size);
			res = pstmt.executeQuery();
			while (res.next()) {
				String userName = res.getString("username");
				String userPass = res.getString("userpass");
				String trueName = res.getString("truename");
				String sex = res.getString("sex");
				String province = res.getString("province");
				String city = res.getString("city");
				String cardType = res.getString("cardtype");
				String idNumber = res.getString("idnumber");
				String passengerType = res.getString("passengertype");
				String tips = res.getString("tips");
				Date birthDate = res.getDate("birthdate");
				UserInfo userInfo = new UserInfo(userName, userPass, trueName, sex, province, city, cardType, idNumber, passengerType, tips, birthDate);
				list.add(userInfo);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		return list;
	}

	@Override
	public boolean deleteByIds(String[] ids) {
		boolean ret = false;
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		connection = DBHelper.openConnection();
		//超级管理员不能删除
		String sql = "  delete from sysuser where username=? and userPower!=-1 ";
		try {
			pstmt = connection.prepareStatement(sql);
			// 按批次添加参数
			for (String userName : ids) {
				pstmt.setString(1, userName);
				pstmt.addBatch();
			}
			// 批量更新时，可能存在部分数据没有更新成功；需要根据数组的返回值进行判断
			// executeBatch会返回一个包含有大于或等于0的数组，数组元素的值是大于0表示更新成功，0表示更新失败
			//             或返回Statement.SUCCESS_NO_INFO   
			int[] counts = pstmt.executeBatch();
			// 本例不做事务控制，只提示更新行数
			int success = 0;
			for (int c : counts) {
				System.out.println(Statement.SUCCESS_NO_INFO + " ++++++++++++++++++++==  " + c);
				if (c == Statement.SUCCESS_NO_INFO || c > 0) {
					success++;
				}
			}
			System.out.println("成功删除: " + success + " 条记录！");
			ret = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, pstmt, connection);
		}
		return ret;
	}

	@Override
	public boolean addUser(UserInfo userInfo) {
		boolean ret=false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "insert into SYSUSER (username, userpass, truename, sex, province, city, cardtype, idnumber, passengertype, tips, birthdate,userPower) values (?,?,?,?,?,?,?,?,?,?,?,?)";
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userInfo.getUserName());
			pstmt.setString(2, userInfo.getUserpass());
			pstmt.setString(3, userInfo.getTrueName());
			pstmt.setString(4, userInfo.getSex());
			pstmt.setString(5, userInfo.getProvince());
			pstmt.setString(6, userInfo.getCity());
			pstmt.setString(7, userInfo.getCardType());
			pstmt.setString(8, userInfo.getIdNumber());
			pstmt.setString(9, userInfo.getPassengerType());
			pstmt.setString(10, userInfo.getTips());
			pstmt.setInt(12, userInfo.getUserPower());
			//时间精度只保留年月日，可以使用java.sql.Date()保存数据，将util.Date转换为sql.Date
			pstmt.setDate(11, new java.sql.Date(userInfo.getBirthDate().getTime()));
			int count = pstmt.executeUpdate();
			if (count==1) {
				ret = true;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		
		return ret;
	}

	@Override
	public UserInfo findUserInfoByid(String userName) {
		
		UserInfo userInfo = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "select username, userpass, truename, sex, province, city, cardtype, "
				+ "idnumber, passengertype, tips, birthdate,userPower,headImage from sysuser where userName=? ";
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userName);
			res = pstmt.executeQuery();
			if(res.next()) {
				//String name = res.getString("username");
				String userPass = res.getString("userpass");
				String trueName = res.getString("truename");
				String sex = res.getString("sex");
				String province = res.getString("province");
				String city = res.getString("city");
				String cardType = res.getString("cardtype");
				String idNumber = res.getString("idnumber");
				String passengerType = res.getString("passengertype");
				String tips = res.getString("tips");
				Date birthDate = res.getDate("birthdate");
				int userPower = res.getInt("userpower");
				String headImage = res.getString("headimage");
				userInfo = new UserInfo(userName, userPass, trueName, sex, province, city, cardType, idNumber, passengerType, tips, birthDate,userPower,headImage);
				
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		return userInfo;
	}

	@Override
	public boolean editUserInfo(UserInfo userInfo) {
		boolean ret=false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "update  SYSUSER set truename=?, sex=?, province=?, city=?, cardtype=?, idnumber=?, passengertype=?,"
				+ " tips=?,birthdate=?,userpower=?,headimage=?  where username=? and userpower!=-1";
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userInfo.getTrueName());
			pstmt.setString(2, userInfo.getSex());
			pstmt.setString(3, userInfo.getProvince());
			pstmt.setString(4, userInfo.getCity());
			pstmt.setString(5, userInfo.getCardType());
			pstmt.setString(6, userInfo.getIdNumber());
			pstmt.setString(7, userInfo.getPassengerType());
			pstmt.setString(8, userInfo.getTips());
			//时间精度只保留年月日，可以使用java.sql.Date()保存数据，将util.Date转换为sql.Date
			pstmt.setDate(9, new java.sql.Date(userInfo.getBirthDate().getTime()));
			pstmt.setInt(10, userInfo.getUserPower());
			pstmt.setString(11, userInfo.getHeadImage());
			pstmt.setString(12, userInfo.getUserName());
			
			int count = pstmt.executeUpdate();
			if (count==1) {
				ret = true;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		
		return ret;
	}

	@Override
	public UserInfo findByIdNumber(String idNumber) {
		UserInfo userInfo = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "select username, userpass, truename, sex, province, city, cardtype, "
				+ "idnumber, passengertype, tips, birthdate,userPower,headImage from sysuser where idnumber=? ";
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, idNumber);
			res = pstmt.executeQuery();
			if(res.next()) {
				String name = res.getString("username");
				String userPass = res.getString("userpass");
				String trueName = res.getString("truename");
				String sex = res.getString("sex");
				String province = res.getString("province");
				String city = res.getString("city");
				String cardType = res.getString("cardtype");
				//String id = res.getString("idnumber");
				String passengerType = res.getString("passengertype");
				String tips = res.getString("tips");
				Date birthDate = res.getDate("birthdate");
				int userPower = res.getInt("userpower");
				String headImage = res.getString("headimage");
				userInfo = new UserInfo(name, userPass, trueName, sex, province, city, cardType, idNumber, passengerType, tips, birthDate,userPower,headImage);
				
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		return userInfo;
	}


	

}
